package paq_sentencias;

import com.sun.rowset.CachedRowSetImpl;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;

public class Ejecutarsql {

    Statement st2 = null;
    Statement st = null;
    Connection con = null;
    Connection conreporte = null;
    ResultSet result1 = null;
    ResultSet result2 = null;
    ResultSet result3 = null;
    ResultSet rs = null;
    List l1 = null;
    public String men;
    String men2 = "";

    public String getMen() {
        return men;
    }

    public void setMen(String men) {
        this.men = men;
    }

    public void ejecutar2(String sql) {
        try {
            InitialContext ic = new InitialContext();
            DataSource ds = (DataSource) ic.lookup("pool_proyecto");
            con = ds.getConnection();
            st = con.createStatement();
            st.executeUpdate(sql);
            con.commit();
            st.close();
            con.close();
            setMen("Transacción Exitosa");
            System.out.println("Exitoso...");
        } catch (SQLException ex) {
            Logger.getLogger(Ejecutarsql.class.getName()).log(Level.SEVERE, null, ex);
            try {
                con.rollback();
            } catch (SQLException ex1) {
                Logger.getLogger(Ejecutarsql.class.getName()).log(Level.SEVERE, null, ex1);
                System.out.println("No es posible realizar un rolback!");
            }
            setMen(ex.getMessage());
            System.out.println("no Exitoso...");

        } catch (NamingException ex) {
            Logger.getLogger(Ejecutarsql.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            try {
                st.close();
                con.close();
                System.out.println("Conexion Cerrada con Exito...");
            } catch (SQLException ex) {
                Logger.getLogger(Ejecutarsql.class.getName()).log(Level.SEVERE, null, ex);
                try {
                    con.rollback();
                } catch (SQLException ex1) {
                    Logger.getLogger(Ejecutarsql.class.getName()).log(Level.SEVERE, null, ex1);
                    System.out.println("No es posible realizar un rolback!");
                }
            }
        }

    }

    public Connection reportes() {
        try {
            InitialContext ic = new InitialContext();
            DataSource ds = (DataSource) ic.lookup("pool_proyecto");
            try {
                conreporte = ds.getConnection();
            } catch (SQLException ex) {

                System.out.println("error1");
            }
            return conreporte;
        } catch (NamingException ex) {
            System.out.println("error2");
            return null;
        }

    }

    public void cerrar_conexion_reporte() {
        if (conreporte != null) {
            try {
                conreporte.close();
                System.out.println("conexion cerrada..");
            } catch (SQLException ex) {
                System.out.println("No se pudo cerrar la Conexión..");
            }
        } else {
            System.out.println("La Conexión ya se cerro anteriormente..");
        }
    }

    public CachedRowSetImpl rellena2(String query) throws Exception {
        InitialContext ic = new InitialContext();
        DataSource ds = (DataSource) ic.lookup("pool_proyecto");
        CachedRowSetImpl crs = null;
        Connection conBBDD = null;
        try {
            conBBDD = ds.getConnection();
            crs = new CachedRowSetImpl();
            crs.setCommand(query);
            crs.execute(conBBDD);
        } catch (Exception e) {
            System.out.println("Error al ejecutar el comando.." + e.getMessage());
        } finally {
            if (conBBDD != null) {
                try {
                    conBBDD.close();
                } catch (SQLException e) {
                    System.out.println("Error al cerrar la Conexión.." + e.getMessage());
                }
            }
        }
        return crs;
    }
}
